IF OBJECT_ID('fGetWebXmlString') IS NOT NULL 
BEGIN 
    DROP FUNCTION fGetWebXmlString
    IF OBJECT_ID('fGetWebXmlString') IS NOT NULL
        PRINT '<<< FAILED DROPPING FUNCTION fGetWebXmlString >>>'
    ELSE 
        PRINT '<<< DROPPED FUNCTION fGetWebXmlString >>>'
END
GO

CREATE FUNCTION dbo.fGetWebXmlString
    (
        @Name   varchar(32) = 'SegmentBranch'
    ,   @ItemId int         = NULL
 )
RETURNS varchar(MAX)
AS
/*
    Description: Returns various XML strings for conversion to real XML.
    Developer: Bryan Wilhite
*/
BEGIN
    DECLARE
        @XmlString varchar(MAX)
    ,   @ParentId  int
    ,   @LoopCount int
    ,   @LoopMax int

    SET @LoopCount = 0
    SET @LoopMax = 10000

    IF @Name = 'DocumentKeywordSearch'
    BEGIN
        SET @XmlString = 
        (
            SELECT
                Keyword = ISNULL(KeywordValue,'')
            FROM
                WebKeyword AS Keywords
            WHERE
                Keywords.DocumentId = @ItemId
            FOR XML AUTO, ELEMENTS, ROOT('SearchResult')
        )
    END

    IF @Name = 'DocumentBranch'
    BEGIN
        SELECT
            @XmlString =
                '<Document>'
                    + '<DocumentId>' + CAST(DocumentId AS varchar(32)) + '</DocumentId>'
                    + '<Title>' + Title + '</Title>'
                    +
                '</Document>'
        FROM
            Document
        WHERE
            DocumentId = @ItemId

        SELECT @ParentId = SegmentId FROM Document WHERE DocumentId = @ItemId

        WHILE (@ParentId IS NOT NULL) AND (@LoopCount < @LoopMax)
        BEGIN
            SET @LoopCount = @LoopCount + 1

            SET @ItemId = @ParentId

            SELECT
                @ParentId = ParentSegmentId
            ,   @XmlString =
                    '<Segment>'
                        + '<SegmentId>' + CAST(SegmentId AS varchar(32)) + '</SegmentId>'
                        + '<SegmentName>' + SegmentName + '</SegmentName>'
                        +
                    '</Segment>' + @XmlString
            FROM
                Segment
            WHERE
                SegmentId = @ItemId
        END    
    END

    IF @Name = 'FragmentBranch'
    BEGIN
        SELECT
            @ParentId = DocumentId
        ,   @XmlString =
                '<Fragment>'
                    + '<FragmentId>' + CAST(FragmentId AS varchar(32)) + '</FragmentId>'
                    + '<ClientId>' + ISNULL(ClientId,'') + '</ClientId>'
                    + '<FragmentName>' + ISNULL(FragmentName,'') + '</FragmentName>'
                    + '<FragmentDisplayName>' + ISNULL(FragmentDisplayName,'') + '</FragmentDisplayName>'
                    +
                '</Fragment>'
        FROM
            Fragment
        WHERE
            FragmentId = @ItemId

        SELECT
            @XmlString =
                '<Document>'
                    + '<DocumentId>' + CAST(DocumentId AS varchar(32)) + '</DocumentId>'
                    + '<Title>' + Title + '</Title>'
                    +
                '</Document>' + @XmlString
        FROM
            Document
        WHERE
            DocumentId = @ParentId

        SELECT @ParentId = SegmentId FROM Document WHERE DocumentId = @ParentId

        WHILE (@ParentId IS NOT NULL) AND (@LoopCount < @LoopMax)
        BEGIN
            SET @LoopCount = @LoopCount + 1

            SET @ItemId = @ParentId

            SELECT
                @ParentId = ParentSegmentId
            ,   @XmlString =
                    '<Segment>'
                        + '<SegmentId>' + CAST(SegmentId AS varchar(32)) + '</SegmentId>'
                        + '<SegmentName>' + SegmentName + '</SegmentName>'
                        +
                    '</Segment>' + @XmlString
            FROM
                Segment
            WHERE
                SegmentId = @ItemId
        END    
    END

    IF @Name = 'FragmentWrapper'
    BEGIN
        SELECT
            @XmlString = '<PrevFragment>' + ISNULL((SELECT FragmentDisplayName FROM Fragment WHERE FragmentId = f.PrevFragmentId),'') + '</PrevFragment>'
        FROM
            Fragment AS f
        WHERE
            f.FragmentId = @ItemId

        SELECT
            @XmlString = '<NextFragment>' + ISNULL((SELECT FragmentDisplayName FROM Fragment WHERE FragmentId = f.NextFragmentId),'') + '</NextFragment>' + @XmlString
        FROM
            Fragment AS f
        WHERE
            f.FragmentId = @ItemId
    END

    IF @Name = 'SegmentBranch'
    BEGIN
        SELECT
            @XmlString =
                '<Segment>'
                    + '<SegmentId>' + CAST(SegmentId AS varchar(32)) + '</SegmentId>'
                    + '<SegmentName>' + SegmentName + '</SegmentName>'
                    +
                '</Segment>'
        FROM
            Segment
        WHERE
            SegmentId = @ItemId

        SELECT @ParentId = ParentSegmentId FROM Segment WHERE SegmentId = @ItemId

        WHILE (@ParentId IS NOT NULL) AND (@LoopCount < @LoopMax)
        BEGIN
            SET @LoopCount = @LoopCount + 1

            SET @ItemId = @ParentId

            SELECT
                @ParentId = ParentSegmentId
            ,   @XmlString =
                    '<Segment>'
                        + '<SegmentId>' + CAST(SegmentId AS varchar(32)) + '</SegmentId>'
                        + '<SegmentName>' + SegmentName + '</SegmentName>'
                        +
                    '</Segment>' + @XmlString
            FROM
                Segment
            WHERE
                SegmentId = @ItemId
        END    
    END

RETURN @XmlString
END
GO

GRANT EXECUTE ON fGetWebXmlString TO public
GO

IF OBJECT_ID('fGetWebXmlString') IS NOT NULL 
    PRINT '<<< CREATED FUNCTION fGetWebXmlString >>>' 
ELSE 
    PRINT '<<< FAILED CREATING FUNCTION fGetWebXmlString >>>' 
GO
